﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using Jalal.Library.DataStructure;
using System.Data;
using System.Data.SqlTypes;
using Jalal.Library.Web;

namespace Jalal.Library.Data
{
    public abstract class DataMiner
    {
        protected DbConnection _connection;
        protected abstract DbConnection Connection
        {
            get;
        }

        public void BeginTransaction( bool openOnly)
        {
            if (Connection.State != ConnectionState.Open)
                Connection.Open();
            else
            {
                Connection.Close();
                Connection.Open();
            }
            if (!openOnly)
                SessionManager.TransactionKey = Connection.BeginTransaction();
            else
                SessionManager.TransactionKey = null; 
        }

        public void RollBackTransaction()
        {
            DbTransaction transactionKey = SessionManager.TransactionKey;
            if (transactionKey != null)
                transactionKey.Rollback();

            Connection.Close();
        }

        public void CommitTransaction()
        {
            DbTransaction transactionKey = SessionManager.TransactionKey;
            if (transactionKey != null)
                transactionKey.Commit(); 

            Connection.Close();
        }

        public DataTable GetListData(DbCommand command)
        {
            DataTable result = new DataTable();
           
            command.CommandType = CommandType.StoredProcedure;
            command.Connection = Connection;
            DbDataReader reader = command.ExecuteReader();
            result.Load(reader);

            return result;
        }

        public object GetOneData(DbCommand command)
        {
            object result;

            command.CommandType = CommandType.StoredProcedure;
            command.Connection = Connection;
            result = command.ExecuteScalar();

            return result;
        }

        public void Execute(List<DbCommand> commands)
        {
            foreach (DbCommand command in commands)
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = Connection;
                command.Transaction = SessionManager.TransactionKey;
                command.ExecuteNonQuery();
            }
        }

        public void Execute(DbCommand command, out object result)
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Connection = Connection;
            command.Transaction = SessionManager.TransactionKey;
            command.ExecuteNonQuery();
        
            if (command.Parameters["ReturnValue"] != null)
                result = command.Parameters["ReturnValue"].Value;
            else
                result = null;
        }

        /// <summary>
        /// Formats the passed numbers with xml string, with <Id> tag, so that 
        /// there numbers can be parsed by SQL Server 2005 stored procedures as an array of Id's.
        /// </summary>
        /// <param name="idsToList"></param>
        /// <returns></returns>
        public static string FormatXMLForIdArray(List<int> idsToList)
        {
            //converting the list to xml first
            StringBuilder xmlString = new StringBuilder();
            for (int i = 0; i < idsToList.Count; i++)
            {
                xmlString.AppendFormat("<Id>{0}</Id>", idsToList[i]);
            }

            return xmlString.ToString();
        }

        public static string FormatXMLForIdArray(List<decimal> idsToList)
        {
            //converting the list to xml first
            StringBuilder xmlString = new StringBuilder();
            for (int i = 0; i < idsToList.Count; i++)
            {
                xmlString.AppendFormat("<Id>{0}</Id>", idsToList[i]);
            }

            return xmlString.ToString();
        }

        /// <summary>
        /// Formats the passed numbers with xml string, with <Id> tag, so that 
        /// there numbers can be parsed by SQL Server 2005 stored procedures as an array of Id's.
        /// </summary>
        /// <param name="idsToList"></param>
        /// <returns></returns>
        public static string FormatXMLForIdArray(List<SqlInt32> idsToList)
        {
            //converting the list to xml first
            StringBuilder xmlString = new StringBuilder();
            for (int i = 0; i < idsToList.Count; i++)
            {
                xmlString.AppendFormat("<Id>{0}</Id>", idsToList[i]);
            }

            return xmlString.ToString();
        }

        public static string FormatXMLForIdArray(List<SqlGuid> idsToList)
        {
            //converting the list to xml first
            StringBuilder xmlString = new StringBuilder();
            for (int i = 0; i < idsToList.Count; i++)
            {
                xmlString.AppendFormat("<Id>{0}</Id>", idsToList[i]);
            }

            return xmlString.ToString();
        }

        public abstract void AddParameter(DbCommand command, string parameterId, DbType parameterType,
            int parameterSize, ParameterDirection parameterDirection, object parameterValue);
    }
}
